fk8 () { local Tbl=$1 local Padding=$2 local -i Level=$3 local TblFKFld local t local ChldFKFld local ChldIdx local -i Cnt=0 local sql="SELECT this_column, REPLACE(downstream_table, '\"', ''), downstream_column, downstream_index FROM dba.get_fk_referenced_by('$Tbl');" if [ $Level = 0 ]; then Level=1; fi if [ -z $Padding ]; then Padding="+++"; fi while IFS=$'\t' read -r -a R do Cnt=$((Cnt+1)) TblFKFld=${R[0]} t=${R[1]} ChldFKFld=${R[2]} ChldIdx="${R[3]}" printf "%10d%02d %s %-35s %-30s %-30s\n" $Level $Cnt $Padding $t $ChldFKFld "$ChldIdx" fk8 $t "${Padding}++++" $((Level*100+Cnt)) done < <(psql CDSLBXW -XtAF$'\t' -c "$sql") if [[ $Level = 1 ]] then printf " %10d %s\n" $Level $Tbl fi } /* _p_table_name must be schema.table_name (Example: tms.document) */ CREATE OR REPLACE FUNCTION dba.get_fk_referenced_by(_p_table_name TEXT) RETURNS TABLE (this_column TEXT , downstream_table TEXT , downstream_column TEXT , downstream_index TEXT) LANGUAGE plpgsql AS $$ DECLARE _v_name TEXT; _v_downstream_index_name NAME; _c_downstream CURSOR FOR select att2.attname::text AS this_column , cla2.relnamespace::regnamespace::text||'.'||co.conrelid::regclass::text AS downstream_table , co.conrelid AS downstream_oid , att1.attname::text AS downstream_column from pg_constraint co inner join pg_class cla1 on co.confrelid = cla1.oid inner join pg_class cla2 on co.conrelid = cla2.oid inner join pg_attribute att1 on co.conrelid = att1.attrelid inner join pg_attribute att2 on co.confrelid = att2.attrelid where co.contype = 'f' and cla1.relnamespace::regnamespace::text||'.'||cla1.relname = _p_table_name and att1.attnum = co.conkey[1] and att2.attnum = co.confkey[1] order by 2; BEGIN _v_name := TRIM(BOTH FROM _p_table_name); PERFORM relname FROM pg_class WHERE relnamespace::regnamespace::text||'.'||relname = _v_name AND relkind = 'r'; IF NOT FOUND THEN downstream_table := _v_name; downstream_column := 'does not exist'; downstream_index := NULL; RETURN NEXT; RETURN; END IF; FOR i IN _c_downstream LOOP this_column := i.this_column; downstream_table := i.downstream_table; downstream_column := i.downstream_column; SELECT indexrelid::regclass::text into downstream_index FROM pg_index INNER JOIN pg_attribute ON indexrelid = attrelid WHERE indrelid = i.downstream_oid AND attname = i.downstream_column; IF downstream_index IS NULL THEN downstream_index := 'ALERT!!! MISSING INDEX'; END IF; RETURN NEXT; END LOOP; END; $$; ============================== $ . fk.sh && fk8 tms.batch 101 +++ cds.rel_x937bundle_batch batch_id idx_cds_rel_x937bundle_batch 102 +++ tms.action batch_id idx_action_batch_id 103 +++ tms.alerted_watchlist batch_id ALERT!!! MISSING INDEX 104 +++ tms.batch_association batch_id idx_batch_association_batch_id 105 +++ tms.batch_clearing_audit batch_id idx_batch_clearing_audit_batch_id 106 +++ tms.batch_format batch_id idx_batch_format_batch_id 10601 +++++++ tms.batch_format_item batch_format_id idx_batch_format_item_batch_format_id 107 +++ tms.batch_item_mapping batch_id idx_batch_item_mapping_batch_id 108 +++ tms.batch_trigger batch_id idx_batch_trigger_batch_id 109 +++ tms.operator batch_id idx_operator_batch_id 110 +++ tms.sub_batch batch_id idx_sub_batch_batch_id 11001 +++++++ cds.cdssubbatch cdssubbatch_id pk_cdssubbatch 11002 +++++++ cds.rel_x937bundle_sub_batch sub_batch_id idx_cds_rel_x937bundle_sub_batch 11003 +++++++ tms.sub_transaction sub_batch_id idx_sub_transaction_sub_batch_id 111 +++ tms.transaction batch_id idx_transaction_batch_id 11101 +++++++ cds.cdstransaction cdstransaction_id pk_cdstransaction 1110101 +++++++++++ cds.rel_cdstransaction_rejectrule cdstransaction_id idx_cds_rel_cdstransaction_rejectrule 11102 +++++++ cds.rel_x937bundle_transaction transaction_id idx_cds_rel_x937bundle_transaction 11103 +++++++ tms.document transaction_id idx_document_transaction_id 1110301 +++++++++++ cds.cdsdocument cdsdocument_id pk_cdsdocument 111030101 +++++++++++++++ cds.rel_cdsdocument_rejectrule cdsdocument_id idx_cds_rel_cdsdocument_rejectrule_documentid 1110302 +++++++++++ tms.docformat document_id idx_docformat_document_id 111030201 +++++++++++++++ tms.docformat_reference format_id idx_docformat_reference_format_id 11103020101 +++++++++++++++++++ tms.docformat_item reference_id idx_docformat_item_reference_id 1110303 +++++++++++ tms.doc_image document_id idx_doc_image_document_id 1110304 +++++++++++ tms.doc_item_mapping document_id idx_doc_item_mapping_document_id 1110305 +++++++++++ tms.mark_sense_detail document_id idx_mark_sense_detail_document_id 11104 +++++++ tms.payment transaction_id idx_payment_transaction_id 1110401 +++++++++++ cds.cdspayment cdspayment_id pk_cdspayment 111040101 +++++++++++++++ cds.rel_cdspayment_rejectrule cdspayment_id idx_cds_rel_cdspayment_rejectrule_paymentid 1110402 +++++++++++ tms.check payment_id idx_check_payment_id 111040201 +++++++++++++++ tms.check_clearing_audit check_id idx_check_clearing_audit_check_id 111040202 +++++++++++++++ tms.check_item_mapping check_id idx_check_item_mapping_check_id 111040203 +++++++++++++++ tms.check_reference check_id idx_check_reference_check_id 11104020301 +++++++++++++++++++ tms.check_item reference_id idx_check_item_reference_id 111040204 +++++++++++++++ tms.gl_ticket_image check_id idx_gl_ticket_image_check_id 1110403 +++++++++++ tms.chk_image payment_id idx_chk_image_payment_id 1110404 +++++++++++ tms.chk_original_image payment_id idx_chk_original_image_payment_id 1110405 +++++++++++ tms.credit_card payment_id idx_credit_card_payment_id 1110406 +++++++++++ tms.float payment_id idx_float_payment_id 11105 +++++++ tms.sub_transaction transaction_id idx_sub_transaction_transaction_id 1 tms.batch